import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Scanner;


/**
 * Classe que define varias funcoes de insert e delete que servem para manipular a base de dados durante a apresentacao
 */
public class ManipulacaoDaBaseDeDados {
	private static Connection connection = null;

	public static void main(String[] args) {
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
			System.exit(1);
		}
		
		Scanner leitor = new Scanner(System.in);
		
		System.out.print("Username: ");
		String username = leitor.nextLine();
		
		System.out.print("Password: ");
		String password = leitor.nextLine();
		
		String connectionURL = "jdbc:jtds:sqlserver://sqlalunos.fe.up.pt/inline2;user="+
		username+";password="+password+";domain=FEUPSIG;ssl=request";

		try {
			connection = DriverManager.getConnection(connectionURL);
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}

		atenderProximoBilheteServico("2");
	}
	
	public static int ultimoBilheteTirado(String idService) {
		try {
			ResultSet results = executeStatement("SELECT TOP 1 TICK_NUMBER "
					+ "FROM T_QUEUE " + "WHERE ID_SERVICE = " + idService + " "
					+ "ORDER BY TICK_HOUR DESC");

			results.next();

			int ticketNumber = Integer.parseInt(results.getString(1));

			results.close();
			
			return ticketNumber;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return -1;
	}
	
	public static String[] proximoBilheteASerAtendido(String idService) {
		try {
			/*ResultSet results = executeStatement("SELECT TOP 1 TICK_NUMBER, TICK_HOUR "
					+ "FROM T_QUEUE " + "WHERE ID_SERVICE = " + idService + " "
					+ "ORDER BY TICK_HOUR");
*/
			// FIXME: Isto funciona no sql explorer e nao aqui, mas ja funcionou
			ResultSet results = executeStatement("SELECT TOP 1 TICK_NUMBER, TICK_HOUR FROM T_QUEUE WHERE ID_SERVICE = 2	ORDER BY TICK_HOUR");
			results.next();

			String ticketNumber = results.getString(1);
			String ticketHour = results.getString(2);
			

			results.close();
			
			String[] valores = {ticketNumber, ticketHour};
			return valores;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}
	
	public static int ultimoBilheteEmT_RECORDS() {
		try {
			ResultSet results = executeStatement("SELECT TOP 1 ID_RECORD FROM T_RECORDS ORDER BY ID_RECORD DESC");

			results.next();
			
			int ticketNumber = Integer.parseInt(results.getString(1));
			results.close();
			
			return ticketNumber;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return -1;
	}
	
	/**
	 * 
	 * @param idService
	 * @param hour formato da hora '15:09'
	 */
	public static void imprimirNovoBilhete(String idService, String hour) {
		try {
			int numeroNovoBilhete = ultimoBilheteTirado(idService)+1;
			
			boolean results = execute("INSERT INTO T_QUEUE "
					+ "(ID_GROUP, ID_SERVICE, ID_SOURCE_SERVICE, TICK_NUMBER, "
					+ 	"ORIGINAL_SERVICE, EXPECTED_WAIT, TICK_HOUR, REQUEUED, "
					+	"NEW_TICKET, ID_RECORD,ID_SUBRECORD, ENABLED,"
					+ 	"REQUEUE_TIME, ID_MESSAGES_HIST, SMS_ALERT,"
					+ 	"ID_TIMELINE, EXTRA_STATUS, ID_TRACKING_GROUP,"
					+ 	"ID_TRACKING_RECORD, ID_SOURCE_GROUP,"
					+ 	"ORIGINAL_GROUP, EXTRA_INFO, ID_TARGET_USER,"
					+ 	"ID_RECORD2, ID_SUBRECORD2, HAS_PRIORITY) VALUES "
					+ "(1, " + idService + ", - 1, " + numeroNovoBilhete + ", - 1, 4, '2011-06-06 " + hour + "', 0, 0, - 1, - 1, 1, NULL, - 1, 0, - 1, NULL, - 1, - 1, - 1, - 1, - 1, - 1, - 1, - 1, 0)");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void atenderProximoBilheteServico(String idService) {
		//try {
			String[] infoBilheteATirar = proximoBilheteASerAtendido(idService);
			
			Calendar currentCalendar = new GregorianCalendar();
			currentCalendar.add(Calendar.MINUTE, 2);
			
			String horaAtendimento = currentCalendar.get(Calendar.HOUR_OF_DAY) + ":" + currentCalendar.get(Calendar.MINUTE);
			
			int ultimoID_RECORD = ultimoBilheteEmT_RECORDS();
			
//			ResultSet results =  executeQuery("INSERT INTO T_RECORDS " +
//					"(ID_GROUP, ID_RECORD, ID_SUBRECORD, START_HOUR, ID_SERVICE, TYPE, DURATION, TICK_HOUR, EXPECTED_WAIT, SERVICE_CODE, ID_CLIENT," +
//					"DISPLAY_NUMBER, ID_USER, TICK_NUMBER)" +
//					"VALUES (1, "+ultimoID_RECORD+1+", 0, '2011-06-06 "+horaAtendimento+"', 2, 'A', 4, '2011-06-06 "+infoBilheteATirar[1]+"', 0, 'C', 2, 2, 19, "+infoBilheteATirar[0]+")");
//			
//			ResultSet results2 = executeQuery("DELETE FROM T_QUEUE WHERE TICK_NUMBER = "+infoBilheteATirar[0]+" AND TICK_HOUR = '"+infoBilheteATirar[1] + "' AND ID_SERVICE = " + idService);
			
			System.out.println("INSERT INTO T_RECORDS " +
					"(ID_GROUP, ID_RECORD, ID_SUBRECORD, START_HOUR, ID_SERVICE, TYPE, DURATION, TICK_HOUR, EXPECTED_WAIT, SERVICE_CODE, ID_CLIENT," +
					"DISPLAY_NUMBER, ID_USER, TICK_NUMBER)" +
					"VALUES (1, "+(ultimoID_RECORD+1)+", 0, '2011-06-06 "+horaAtendimento+"', 2, 'A', 4, '"+infoBilheteATirar[1]+"', 0, 'C', 2, 2, 19, "+infoBilheteATirar[0]+")");
			
			System.out.println("DELETE FROM T_QUEUE WHERE TICK_NUMBER = "+infoBilheteATirar[0]+" AND TICK_HOUR = '"+infoBilheteATirar[1] + "' AND ID_SERVICE = " + idService);
//
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}

	}
	
	private String addMinutesToTime(String originalTime, String minutesToAdd) {
		
		// Formato da string originalTime: 15:08:00
		int hours = Integer.parseInt(originalTime.substring(0, 2));
		int minutes = Integer.parseInt(originalTime.substring(3, 5));
		
		Calendar originalCalendar = new GregorianCalendar();
		
		originalCalendar.set(Calendar.HOUR_OF_DAY, hours);
		originalCalendar.set(Calendar.MINUTE, minutes);
		
		originalCalendar.add(Calendar.MINUTE, Integer.parseInt(minutesToAdd));
		
		return originalCalendar.get(Calendar.HOUR_OF_DAY) + ":" + originalCalendar.get(Calendar.MINUTE);
	}

	
	private static boolean execute(String query) throws SQLException {
		Statement st = connection.createStatement();
		boolean rs = st.execute(query);
		/*
		while (rs.next()) {
		//	System.out.print("Column 2 returned ");
		//	System.out.println(rs.getString(2));
			TicketQueue tqueue = new TicketQueue(r)
		}*/
		//st.close();

		return rs;
	}
	
	private static ResultSet executeStatement(String query) throws SQLException {
		Statement st = connection.createStatement();
		ResultSet rs = st.executeQuery(query);
		/*
		while (rs.next()) {
		//	System.out.print("Column 2 returned ");
		//	System.out.println(rs.getString(2));
			TicketQueue tqueue = new TicketQueue(r)
		}*/
		//st.close();

		return rs;
	}


}
